/**
 * 
 */
package com.maszy.service;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.maszy.bean.Dept;
import com.maszy.db.DBConnection;

/**
 * @作者 Allen Shu
 * @创建日期 2012-6-6
 * @版本 V 1.0
 */

public class DeptService {
	private static PreparedStatement pst;
	private static ResultSet rs = null;

	public int add(Dept obj) {
		DBConnection con = new DBConnection();
		int result = 0;
		String sql = "INSERT INTO [Maszy_Dept]([dept_name], [print_id],dept_sort, [dept_remark]) VALUES(?,?,?,?)";
		try {
			pst = con.getConnection().prepareStatement(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			pst.setString(1, obj.getDept_name());
			pst.setInt(2, obj.getPrint_id());
			pst.setString(3, obj.getDept_sort());
			pst.setString(4, obj.getRemark());
			result = pst.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return result;
	}

	public int update(Dept obj) {
		DBConnection con = new DBConnection();
		int i = 0;
		String sql = "UPDATE [Maszy_Dept] SET [dept_name]=?, [print_id]=?,dept_sort=?, [dept_remark]=? WHERE dept_id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setString(1, obj.getDept_name());
			pst.setInt(2, obj.getPrint_id());
			pst.setString(3, obj.getDept_sort());
			pst.setString(4, obj.getRemark());
			pst.setInt(5, obj.getDept_id());
			i = pst.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}

	public int delete(Dept obj) {
		DBConnection con = new DBConnection();
		int i = 0;
		String sql = "delete from  Maszy_Dept  where dept_id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setInt(1, obj.getDept_id());
			i = pst.executeUpdate();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;

				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return i;
	}

	public Dept queryById(Dept obj) {
		DBConnection con = new DBConnection();
		String sql = "select a.*,b.* from maszy_dept a left join maszy_print b on a.print_id=b.print_id where a.dept_id=?";
		try {
			pst = con.getConnection().prepareStatement(sql);
			pst.setInt(1, obj.getDept_id());

			rs = pst.executeQuery();
			while (rs.next()) {
				obj = new Dept();
				obj.setDept_id(rs.getInt("dept_id"));
				obj.setDept_name(rs.getString("dept_name"));
				obj.setPrint_id(rs.getInt("print_id"));
				obj.setDept_sort(rs.getString("dept_sort"));
				obj.setPrint_name(rs.getString("print_name"));
				obj.setRemark(rs.getString("dept_remark"));
			}
		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return obj;
	}

	public List<Dept> queryAll() {
		List<Dept> list = new ArrayList<Dept>();
		DBConnection con = new DBConnection();
		String sql = "select a.*,b.* from maszy_dept a left join maszy_print b on a.print_id=b.print_id order by a.dept_sort asc";
		try {
			pst = con.getConnection().prepareStatement(sql);
			rs = pst.executeQuery();

			while (rs.next()) {
				Dept obj = new Dept();
				obj = new Dept();
				obj.setDept_id(rs.getInt("dept_id"));
				obj.setDept_name(rs.getString("dept_name"));
				obj.setPrint_id(rs.getInt("print_id"));
				obj.setDept_sort(rs.getString("dept_sort"));
				obj.setPrint_name(rs.getString("print_name"));
				obj.setRemark(rs.getString("dept_remark"));
				list.add(obj);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}

	public List<Dept> queryAllAndCountProduct(){
		String sql="select a.dept_name,b.print_name,a.dept_sort,count(c.maszy_product_id ) as countProduct    from maszy_dept a left join maszy_print b on a.print_id=b.print_id left join maszy_product c on a.dept_id=c.maszy_product_dept group by a.dept_name,b.print_name,a.dept_sort order by a.dept_sort asc ";
		List<Dept> list = new ArrayList<Dept>();
		DBConnection con = new DBConnection();
		try {
			pst = con.getConnection().prepareStatement(sql);
			rs = pst.executeQuery();

			while (rs.next()) {
				Dept obj = new Dept();
				obj = new Dept();
				obj.setDept_name(rs.getString("dept_name"));
				obj.setDept_sort(rs.getString("dept_sort"));
				obj.setPrint_name(rs.getString("print_name"));
				obj.setCountProduct(rs.getInt("countProduct"));
				list.add(obj);
			}

		} catch (Exception e) {
			System.out.println(e.getMessage());
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (con != null) {
					con.close();
					con = null;
				}
			} catch (Exception e) {
				// TODO 自动生成 catch 块
				e.printStackTrace();
			}
		}
		return list;
	}
}
